/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.hnilica.cisteniulic.emailer;

import net.hnilica.cisteniulic.dto.EmailTermin;
import com.sun.org.apache.xerces.internal.dom.DocumentImpl;
import com.sun.org.apache.xml.internal.serialize.OutputFormat;
import com.sun.org.apache.xml.internal.serialize.XMLSerializer;
import java.io.IOException;
import java.io.StringWriter;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Text;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Locale;
import java.util.Properties;
import java.util.Set;
import java.util.TimeZone;
import java.util.TimerTask;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.hnilica.cisteniulic.dao.ConnectionPool;
import net.hnilica.cisteniulic.dao.DBUtil;
import net.hnilica.cisteniulic.dto.TerminCisOri;

/**
 *
 * @author swosh
 */
public class TermChecker extends TimerTask {

    private static final TermChecker instance = new TermChecker();
    private static final int hour = 1 * 60 * 60 * 1000;
    private static final int day = 24 * 60 * 60 * 1000;
    private final GregorianCalendar gCalendar = new GregorianCalendar(TimeZone.getTimeZone("GMT+01"), Locale.getDefault());
    protected Logger log = Logger.getLogger(getClass().getName());


    public static TermChecker getTermChecker() {
        return instance;
    }

    private TermChecker() {}
    
    @Override
    public void run() {
        TreeSet<EmailTermin> treeSetEmailTermin = (TreeSet) checkTerm();
        if (treeSetEmailTermin.isEmpty()) {
            return;
        }
        String currentEmail = new String();
        TreeSet treeSetTermin = new TreeSet();
        Set<String> setEmail = new HashSet<String>();
        boolean first = true; // First time iteration
        for (Iterator<EmailTermin> it = treeSetEmailTermin.iterator(); it.hasNext();) {
            EmailTermin emailTermin = it.next();
            String email = emailTermin.getEmail();
            TerminCisOri termin = emailTermin.getTermin();

            if (!currentEmail.equals(email)) {
                //if not first time iteration....same block like for last iteration...send it

                if (!first) {
                    sendWarningMail(currentEmail, treeSetTermin);
                    treeSetTermin.clear();
                    setEmail.add(currentEmail);
                } else {
                    first = false;
                }
            }
            treeSetTermin.add(termin);
            currentEmail = email;
            if (!it.hasNext()) {
                sendWarningMail(currentEmail, treeSetTermin);
                treeSetTermin.clear();
                setEmail.add(currentEmail);
            }
        }
        updateLastSeneded(setEmail);

    }

    public Collection checkTerm() {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        TreeSet treeSetEmailTermin = new TreeSet();
        String query = "SELECT termin.naz_ulice,termin.termin_cisteni,info.email FROM termin "
                + "join ulice on (ulice.naz_ulice = termin.naz_ulice)"
                + "join info on (ulice.naz_ulice = info.naz_ulice)"
                + "join osoba on (info.email = osoba.email)"
                + "WHERE termin.termin_cisteni <= ? AND osoba.naposledy_poslano < ? ";
        Date today = gCalendar.getTime();
        Date nextWeek = new Date(today.getTime() + (7 * day));
        Date lastWeek = new Date(today.getTime() - (7 * day));
        Timestamp nextWeekTS = new Timestamp(nextWeek.getTime());
        Timestamp lastWeekTS = new Timestamp(lastWeek.getTime());
        try {
            ps = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            ps.setTimestamp(1, nextWeekTS);
            ps.setTimestamp(2, lastWeekTS);
            rs = ps.executeQuery();
            rs.last();
            int rowCount = rs.getRow();
            rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
            boolean isEmpty = (rowCount == 0) ? true : false;

            if (!isEmpty) {
                while (rs.next()) {
                    TerminCisOri termin = new TerminCisOri(rs.getString("naz_ulice"), rs.getTimestamp("termin_cisteni"));
                    String email = rs.getString("email");
                    EmailTermin emailTermin = new EmailTermin(email, termin);
                    treeSetEmailTermin.add(emailTermin);


                }

            } else {
                log.log(Level.INFO, "Tento den nebyl odeslán žádný upozrňovací email.");

            }
        } catch (SQLException e) {
            log.log(Level.SEVERE, "Došlo k chybě v databázi při kontrole termínů.", e);
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            try {
                connection.close();
            } catch (SQLException e) {
                log.log(Level.SEVERE, "Došlo k chybě v databázi při kontrole termínů.", e);
            }
        }
        return treeSetEmailTermin;

    }

    /**
     *
     * @param recipient
     * @param collTermin
     */
    //treeSetTermin must be sorted before
    public void sendWarningMail(String recipient, Collection<TerminCisOri> collTermin) {

        String subject = "Upozornění na čištění";

        TreeSet treeSetTermin = new TreeSet(collTermin);

        int numTermsLeft = treeSetTermin.size();
        Element term = null;
        Text text = null;
        Date currentDate = new Date();
        StringBuilder textSb = new StringBuilder();
        SimpleDateFormat pattern = new SimpleDateFormat("dd. MM. yyyy");
        //root elements
        Document doc = new DocumentImpl();

        Element body = doc.createElement("body");

        Element greeting = doc.createElement("p");
        greeting.appendChild(doc.createTextNode("Dobrý den,"));
        body.appendChild(greeting);

        Element br = doc.createElement("br");
        body.appendChild(br);

        Element introduce = doc.createElement("p");
        introduce.appendChild(doc.createTextNode("Upozorňujeme na čištění ulic"
                + " v následujících termínech a místech: "));
        body.appendChild(introduce);



        for (Iterator<TerminCisOri> it = treeSetTermin.iterator(); it.hasNext();) {
            TerminCisOri termin = it.next();
            Date date = termin.getTerCis();

            // if next date
            if (!currentDate.equals(date)) {
                // If not first
                if (numTermsLeft != treeSetTermin.size()) {
                    textSb.append(".");
                    //
                    text = doc.createTextNode(textSb.toString());
                    textSb.delete(0, textSb.length());

                    term.appendChild(text);

                    body.appendChild(term);
                    body.appendChild(br);
                }
                text = doc.createTextNode("");//empty
                term = doc.createElement("p");//empty
                textSb.append(pattern.format(date) + " proběhne čištění v ulici/ích ");
            } else {
                //if same date
                textSb.append(", ");
            }
            textSb.append(termin.getNazUlice());
            currentDate = date;
            numTermsLeft--;
            //if last
            if (!it.hasNext()) {
                textSb.append(".");
                //
                text = doc.createTextNode(textSb.toString());
                textSb.delete(0, textSb.length());

                term.appendChild(text);

                body.appendChild(term);
                body.appendChild(br);
            }

        }

        Element end = doc.createElement("p");
        Text textEnd1 = doc.createTextNode("S pozdravem,");
        end.appendChild(textEnd1);
        end.appendChild(br);
        Text textEnd2 = doc.createTextNode("Petr Hnilica");
        end.appendChild(textEnd2);
        body.appendChild(end);
        doc.appendChild(body);

        OutputFormat format = new OutputFormat(doc);
        // as a String
        StringWriter stringOut = new StringWriter();
        XMLSerializer serial = new XMLSerializer(stringOut,
                format);
        try {
            serial.serialize(doc);
        } catch (IOException ex) {
            log.log(Level.SEVERE, null, ex);
        }



        try {
        } catch (Exception ex) {
            log.log(Level.SEVERE, "Look up error", ex);
        }
        Properties props = System.getProperties();
        props.put("mail.smtp.host", "smtp.gmail.com");
        props.put("mail.transport.protocol", "smtps");
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.port", "465");
        props.put("mail.smtp.user", "cistic.ulic");
        props.put("mail.smtp.from", "cistic.ulic@gmail.com");
        props.put("mail.smtp.quitwait", "false");
        props.put("mail.smtp.starttls.enable", "true");
        props.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
        Session session = Session.getInstance(props, new javax.mail.Authenticator() {

            @Override
            protected PasswordAuthentication getPasswordAuthentication() {
                return new PasswordAuthentication("cistic.ulic", "vltavag13c");
            }

        });
        Message message = new MimeMessage(session);

        try {
            InternetAddress to[] = new InternetAddress[1];
            to[0] = new InternetAddress(recipient);
            message.setRecipients(Message.RecipientType.TO, to);
            message.setSubject(subject);
            message.setContent(stringOut.toString(), "text/html; charset=UTF-8");
            message.saveChanges();
            Transport.send(message);
        } catch (AddressException ex) {
            log.log(Level.SEVERE, null, ex);
        } catch (MessagingException ex) {
            log.log(Level.SEVERE, null, ex);
        }
    }

    public void updateLastSeneded(Collection collEmail) {
        // TODO connection pool
        Set<String> setEmail = new HashSet<String>(collEmail);
        StringBuilder sB = new StringBuilder();
        boolean first = true;
        for (Iterator<String> it = setEmail.iterator(); it.hasNext();) {
            String email = it.next();

            if (first) {
                first = false;
                sB.append("email = ");
                sB.append("'");
                sB.append(email);
                sB.append("'::character varying");
            } else {
                sB.append(" OR ");
                sB.append("email = ");
                sB.append("'");
                sB.append(email);
                sB.append("'::character varying");
            }
        }
        String emails = sB.toString();
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        int numUpdated = 0;
        String query = "UPDATE osoba SET naposledy_poslano = ? WHERE " + emails; //email are under control no sql injection
        Date today = gCalendar.getTime();
        //for certenity. Time must be lowerd because there is delay.
        //And next selection naposledy_zmeneno would be after selected checking time by several seconds
        Timestamp naposledyPoslano = new Timestamp(today.getTime() - 2 * hour);



        try {
            ps = connection.prepareStatement(query);

            ps.setTimestamp(1, naposledyPoslano);
            numUpdated = ps.executeUpdate();


            if (numUpdated == 0) {
                log.log(Level.SEVERE, "Nebylo pozměněno datum. Nemělo by nastat.");
            }
        } catch (SQLException e) {
            log.log(Level.SEVERE, "Došlo k chybě v databázi při změně naposledy_poslano.", e);
        } finally {
            DBUtil.closePreparedStatement(ps);
            try {
                connection.close();
            } catch (SQLException e) {
                log.log(Level.SEVERE, "Došlo k chybě v databázi při změně naposledy_poslano.", e);
            }
        }

    }

}
